/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.models.impl;

import com.ntb.models.*;
import com.ntb.common.DatabaseConnection;
import com.ntb.entities.Flats;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

/**
 *
 * @author AnhTuan
 */
public class FlatsDAOImpl extends DatabaseConnection implements FlatsDAO {

    public int countAll() {
        try {
            openConnection();
            ResultSet rs = getResultset("Select Count(FlatID) from Flats");
            int result = 0;
            if (rs.next()) {
                result = rs.getInt(1);
            }
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public Iterator<Flats> getList() {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            ResultSet rs = getResultset("Select * From Flats");
            while (rs.next()) {
                Flats row = new Flats();
                row.setFlatID(rs.getInt("FlatID"));
                row.setBuildingID(rs.getInt("BuildingID"));
                row.setFlatName(rs.getString("FlatName"));
                row.setFloorNumber(rs.getInt("FloorNumber"));
                row.setArea(rs.getInt("Area"));
                row.setDescription(rs.getString("Description"));
                row.setStatus(rs.getInt("Status"));
                list.add(row);
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public Iterator<Flats> getList(String flatId, String flatName, String buildingId, String buildingName, int floorFrom, int floorTo, int areaFrom, int areaTo, int page) {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_pageFlats(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            cst.setString(1, flatId);
            cst.setString(2, flatName);
            cst.setString(3, buildingId);
            cst.setString(4, buildingName);
            cst.setInt(5, floorFrom);
            cst.setInt(6, floorTo);
            cst.setInt(7, areaFrom);
            cst.setInt(8, areaTo);
            cst.setInt(9, page);
            cst.setInt(10, itemsPerPage);

            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                Flats row = new Flats();
                row.setFlatID(rs.getInt("FlatID"));
                row.setFlatName(rs.getString("FlatName"));
                row.setBuildingID(rs.getInt("BuildingID"));
                row.setBuildingName(rs.getString("BuildingName"));
                row.setFloorNumber(rs.getInt("FloorNumber"));
                row.setArea(rs.getInt("Area"));

                list.add(row);
            }
            closeConnection();
            if (list.size() > 0) {
                return list.iterator();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int getPageCount(String flatId, String flatName, String buildingId, String buildingName, int floorFrom, int floorTo, int areaFrom, int areaTo) {
        try {
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countPageFlats(?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            cst.setString(1, flatId);
            cst.setString(2, flatName);
            cst.setString(3, buildingId);
            cst.setString(4, buildingName);
            cst.setInt(5, floorFrom);
            cst.setInt(6, floorTo);
            cst.setInt(7, areaFrom);
            cst.setInt(8, areaTo);
            cst.setInt(9, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt("Count");
            }
            closeConnection();

            return count;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public Flats getRow(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement(
                    "SELECT Flats.*, Flats.Area * Locations.PresentCost AS Cost FROM Flats " +
                    "JOIN Buildings " +
                    "ON Flats.BuildingID = Buildings.BuildingID " +
                    "JOIN Lands " +
                    "ON Buildings.LandID = Lands.LandID " +
                    "JOIN Locations " +
                    "ON Lands.LocationID = Locations.LocationID " +
                    "WHERE FlatID = ?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            Flats row = new Flats();
            if (rs.next()) {
                row.setFlatID(rs.getInt("FlatID"));
                row.setBuildingID(rs.getInt("BuildingID"));
                row.setFlatName(rs.getString("FlatName"));
                row.setFloorNumber(rs.getInt("FloorNumber"));
                row.setArea(rs.getInt("Area"));
                row.setDescription(rs.getString("Description"));
                row.setCost(rs.getLong("cost"));

            }
            closeConnection();
            return row;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int getCost(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement(
                    "SELECT Flats.Area * Locations.PresentCost as Cost FROM Flats " +
                    "LEFT JOIN Buildings " +
                    "ON Flats.BuildingID = Buildings.BuildingID " +
                    "LEFT JOIN Lands " +
                    "ON Buildings.LandID = Lands.LandID " +
                    "LEFT JOIN Locations " +
                    "ON Lands.LocationID = Locations.LocationID " +
                    "WHERE FlatID = ?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            int result = -1;
            if (rs.next()) {
                result = rs.getInt("Cost");
            }
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public boolean checkExist(int flatId) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select FlatID From Flats Where FlatID=?");
            pst.setInt(1, flatId);
            ResultSet rs = pst.executeQuery();
            boolean result = rs.next();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public int add(Flats object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Insert Into Flats(BuildingID,FlatName,FloorNumber,Area,Description,Status) Values(?,?,?,?,?,?)");
            pst.setInt(1, object.getBuildingID());
            pst.setString(2, object.getFlatName());
            pst.setInt(3, object.getFloorNumber());
            pst.setInt(4, object.getArea());
            pst.setString(5, object.getDescription());
            pst.setInt(6, object.getStatus());
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int update(Flats object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Update Flats Set FlatName=?,FloorNumber=?,Area=?,Description=?,Status=? Where FlatID=?");
            pst.setString(1, object.getFlatName());
            pst.setInt(2, object.getFloorNumber());
            pst.setInt(3, object.getArea());
            pst.setString(4, object.getDescription());
            pst.setInt(5, object.getStatus());
            pst.setInt(6, object.getFlatID());
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int delete(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Delete Flats Where FlatID = ? " +
                    "AND ? NOT IN (SELECT FlatID FROM Sales)");
            pst.setInt(1, id);
            pst.setInt(2, id);
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int deleteIn(String arrayIds) {
        try {
            openConnection();
            String sql = "Delete Flats Where FlatID in (" + arrayIds + ")";
            Statement st = conn.createStatement();
            int result = st.executeUpdate(sql);
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }
}
